package org.thotheolh.sweatkeeper.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.thotheolh.sweatkeeper.helper.*;

public class SQLManager {

	Connection conn = null;
	PreparedStatement stmt;
	ResultSet rs;
	int numexec;
	String JDBCUrl;
	String setupUrl;
	String setupScript;

	public SQLManager(String baseDir) {
		JDBCUrl = "jdbc:h2:file:" + baseDir + "/DBStore/SweatKeeper";
		setupUrl = baseDir + "/setup/";
		setupScript = setupUrl + "sweatkeeper_setup.sql";
		conn = new DbConnection(JDBCUrl).getConnection();
		if (isConnected()){
			onLoad();
		}
	}

	// When the databases load... do these...
	private void onLoad() {

		// If upon check, database tables are not intact...
		if (!isDatabaseExist()) {
			try {
				// Create new database all over again...
				stmt = conn.prepareStatement("DROP ALL OBJECTS"); // SQL to wipe any remaining database objects
				stmt.executeUpdate();
				stmt = conn.prepareStatement("RUNSCRIPT FROM '" + setupScript +"'");
				stmt.executeUpdate();
				if(!isDatabaseExist()){
					System.out.println("Database: " + isDatabaseExist() +"\nUnable to setup database !!!");
				}
			}
			catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	// Checks if database tables are intact.
	public boolean isDatabaseExist() {
		// Database flags
		boolean isWorkout = false;
		boolean isRunWorkout = false;
		boolean isGymWorkout = false;
		boolean isEquipment = false;

		try {
			stmt = conn.prepareStatement("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'");
			rs = stmt.executeQuery();
			while (rs.next()) {
				String table = rs.getString("TABLE_NAME");
				if (table.equalsIgnoreCase("WORKOUT")) {
					isWorkout = true;
				}
				if (table.equalsIgnoreCase("RUNWORKOUT")) {
					isRunWorkout = true;
				}
				if (table.equalsIgnoreCase("GYMWORKOUT")) {
					isGymWorkout = true;
				}
				if (table.equalsIgnoreCase("EQUIPMENT")) {
					isEquipment = true;
				}
			}
			
			if ((!isWorkout) || (!isRunWorkout) || (!isGymWorkout) || (!isEquipment)) {
				return false;
			}
			else {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	/**
	 * 
	 * SQL statements below
	 * 
	 */
	
	public Workout getWorkout(int workoutId){
		Workout workout = null;
		try {
			stmt = conn.prepareStatement("SELECT * FROM WORKOUT WHERE WORKOUTID = ?");
			stmt.setInt(1, workoutId);
			rs = stmt.executeQuery();
			if (rs != null){
				while (rs.next()){
					workout = new Workout();
					workout.setWorkoutId(rs.getInt("WORKOUTID"));
					workout.setWorkoutDate(rs.getDate("DATE"));
					workout.setEntryTimestamp(rs.getTimestamp("ENTRYTIMESTAMP"));
					workout.setComments(rs.getString("COMMENTS"));
					workout.setType(rs.getString("TYPE"));
				}
			}			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return workout;
	}
	
	public ArrayList getAllWorkout(){
		ArrayList ls = new ArrayList();
		Workout workout = null;
		try {
			stmt = conn.prepareStatement("SELECT * FROM WORKOUT");
			rs = stmt.executeQuery();
			if (rs != null){
				while (rs.next()){
					workout = new Workout();
					workout.setWorkoutId(rs.getInt("WORKOUTID"));
					workout.setWorkoutDate(rs.getDate("DATE"));
					workout.setEntryTimestamp(rs.getTimestamp("ENTRYTIMESTAMP"));
					workout.setComments(rs.getString("COMMENTS"));
					workout.setType(rs.getString("TYPE"));
					ls.add(workout);
				}
			}			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return ls;
	}
	
	public Equipment getEquipment(int eqid){
		Equipment eq = null;
		try {
			stmt = conn.prepareStatement("SELECT * FROM EQUIPMENT WHERE EQID = ?");
			stmt.setInt(1, eqid);
			rs = stmt.executeQuery();
			if (rs != null){
				while (rs.next()){
					eq = new Equipment();
					eq.setEqId(rs.getInt("EQID"));
					eq.setEqName(rs.getString("EQNAME"));
					eq.setEqComments(rs.getString("EQCOMMENTS"));
				}
			}			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return eq;
	}
	
	public ArrayList getAllEquipments(){
		ArrayList ls = new ArrayList();
		Equipment eq = null;
		try {
			stmt = conn.prepareStatement("SELECT * FROM EQUIPMENT");
			rs = stmt.executeQuery();
			if (rs != null){
				while (rs.next()){

					eq = new Equipment();
					eq.setEqId(rs.getInt("EQID"));
					eq.setEqName(rs.getString("EQNAME"));
					eq.setEqComments(rs.getString("EQCOMMENTS"));
					ls.add(eq);
				}
			}			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return ls;
	}
		
	public boolean addWorkout(Workout workout) {
		try {
			stmt = conn.prepareStatement("INSERT INTO WORKOUT (DATE, ENTRYTIMESTAMP, COMMENTS, TYPE) VALUES (?,?,?,?)");
			stmt.setDate(1, workout.getWorkoutDate());
			stmt.setTimestamp(2, new java.sql.Timestamp(new java.util.Date().getTime()));
			stmt.setString(3, workout.getComments());
			stmt.setString(4, workout.getType());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean addRunWorkout(RunWorkout workout) {
		try {
			stmt = conn.prepareStatement("INSERT INTO RUNWORKOUT VALUES (?,?,?)");
			stmt.setInt(1, workout.getWorkoutId());
			stmt.setDouble(2, workout.getDistance());
			stmt.setTime(3, workout.getTime());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean addGymWorkout(GymWorkout workout) {
		try {
			stmt = conn.prepareStatement("INSERT INTO GYMWORKOUT VALUES (?,?,?,?,?,?,?)");
			stmt.setInt(1, workout.getSetId());
			stmt.setInt(2, workout.getSequenceId());
			stmt.setInt(3, workout.getWorkoutId());
			stmt.setInt(4, workout.getEqId());
			stmt.setDouble(5, workout.getEqWeight());
			stmt.setInt(6, workout.getRep());
			stmt.setTime(7, workout.getTime());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean editWorkout(Workout workout) {
		try {
			stmt = conn.prepareStatement("UPDATE WORKOUT  SET DATE = ?, ENTRYTIMESTAMP = ?, COMMENTS = ?, TYPE = ? WHERE WORKOUTID = ?");
			stmt.setDate(1, workout.getWorkoutDate());
			stmt.setTimestamp(2, new java.sql.Timestamp(new java.util.Date().getTime()));
			stmt.setString(3, workout.getComments());
			stmt.setString(4, workout.getType());
			stmt.setInt(5, workout.getWorkoutId());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean editRunWorkout(RunWorkout workout) {
		try {
			stmt = conn.prepareStatement("UPDATE RUNWORKOUT SET DISTANCE = ?, TIME = ? WHERE WORKOUTID = ?");
			stmt.setInt(3, workout.getWorkoutId());
			stmt.setDouble(1, workout.getDistance());
			stmt.setTime(2, workout.getTime());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean editGymWorkout(GymWorkout workout) {
		try {
			stmt = conn.prepareStatement("UPDATE GYMWORKOUT SET SETID = ?, SEQUENCEID = ?, EQID = ?, EQWEIGHT = ?, REP = ?, TIME = ? WHERE WORKOUTID = ?");
			stmt.setInt(1, workout.getSetId());
			stmt.setInt(2, workout.getSequenceId());
			stmt.setInt(7, workout.getWorkoutId());
			stmt.setInt(3, workout.getEqId());
			stmt.setDouble(4, workout.getEqWeight());
			stmt.setInt(5, workout.getRep());
			stmt.setTime(6, workout.getTime());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean rmWorkout(int workoutid) {
		try {
			stmt = conn.prepareStatement("DELETE FROM WORKOUT WHERE WORKOUTID = ?");
			stmt.setInt(1, workoutid);
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean addEquipment(Equipment equipment) {
		try {
			stmt = conn.prepareStatement("INSERT INTO EQUIPMENT (EQNAME, EQCOMMENTS) VALUES (?,?)");
			stmt.setString(1, equipment.getEqName());
			stmt.setString(2, equipment.getEqComments());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean editEquipment(Equipment equipment) {
		try {
			stmt = conn.prepareStatement("UPDATE EQUIPMENT SET EQNAME = ?, EQCOMMENTS = ? WHERE EQID = ?");
			stmt.setString(1, equipment.getEqName());
			stmt.setString(2, equipment.getEqComments());
			stmt.setInt(3, equipment.getEqId());
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public boolean rmEquipment(int eqid) {
		try {
			stmt = conn.prepareStatement("DELETE FROM EQUIPMENT WHERE EQID = ?");
			stmt.setInt(1, eqid);
			numexec = stmt.executeUpdate();
			if (numexec >= 1) {
				return true;
			}
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
		return false;
	}

	public void close() {
		try {
			conn.close();
		}
		catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public boolean isConnected() {
		if (conn == null) {
			return false;
		}
		try {
			if (conn.isClosed()) {
				return false;
			}
		}
		catch (SQLException ex) {
			return false;
		}
		return true;
	}
}